import java.io.BufferedReader;
import java.io.InputStreamReader;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public class CustomerInfo
{

	public String salutation, lastName, firstName;
	public String streetAddress, city, state;
	public String phoneNumber, email, creditCardNumber, creditCardExpirationYear, creditCardExpirationMonth;
	public int creditCardExpirationDay, cid;
	
	private Connection connection; //used to hold the jdbc connection to the DB
    private Statement statement; //used to create an instance of the connection
    private ResultSet resultSet; //used to hold the result of your query (if one
    // exists)'
    private String query;
	
	public CustomerInfo()
	{
		System.out.println("Hey " + CustomerInterface.username + ". A customer wants to sign up.");	
		
		connectToDB();
		checkIfDBisOpen();		
	}
	
	private void connectToDB()
	{
		try
		{
			// Register the oracle driver.  
			DriverManager.registerDriver (new oracle.jdbc.driver.OracleDriver());
			
			//This is the location of the database.  This is the database in oracle
			//provided to the class
			String url = "jdbc:oracle:thin:@db10.cs.pitt.edu:1521:dbclass"; 
			
			//create a connection to DB on db10.cs.pitt.edu
			this.connection = DriverManager.getConnection(url, CustomerInterface.username, CustomerInterface.password); 
						
			if(this.connection.isClosed())
			{
				System.out.println("It appears the connection to the database is closed.");
			}
			else
			{
				System.out.println("It appears we connected without any problems.");
				System.out.println("It appears the connection to the database was openened.");
			}
			
			
		}
		catch(Exception e)  {
			e.printStackTrace();
			System.out.println("Error connecting to database.  Machine Error: " +
					   e.toString());
		}		
	}
	
	private void closeDBconnection()
	{
		try 
		{
			this.connection.close();
		}
		catch(Exception Ex)  
		{
			System.out.println("Error connecting to database (main of CustomerInterface).  Machine Error: " +
					   Ex.toString());
		}
	}
	
	private void checkIfDBisOpen()
	{
		try
		{
			if(this.connection.isClosed())
			{
				System.out.println("It appears the connection to the database is closed.");
			}
			else
			{
				System.out.println("It appears the database connection is open.");
			}
		}
			
		catch(Exception e)
		{
			System.out.println("Is the database open? Errors: " +
					   e.toString());
			e.printStackTrace();
		}
	}
	
	private void printCustomerInfo()
	{
		System.out.printf("\n\nCustomer Info on hand: \n");
		System.out.printf("salutation: '%s'\nlast name: '%s'\nfirst name: '%s' \n"
				+ "street address: '%s' \ncity: '%s' \nstate: '%s'\nphone number: '%s'\n"
				+ "email: '%s'\ncredit card number: '%s'\nexpiration month: '%s'\n"
				+ "expiration day: '%d'\nexpiration year: '%s'\n", 
				this.salutation, this.lastName, this.firstName, 
				this.streetAddress, this.city, this.state, this.phoneNumber, 
				this.email, this.creditCardNumber, this.creditCardExpirationMonth,
				this.creditCardExpirationDay, this.creditCardExpirationYear);
	}
	
	
	
	public void askCustomerForInfo()
	{
		askCustomerForName();		
		askCustomerForAddress();		
		askCustomerForPhoneNumber();
		askCustomerForEmail();
		askCustomerForCreditCardNumber();
		askCustomerForCreditCardExpirationDates();
		
		printCustomerInfo();
		insertCustomerInfoIntoDatabase();
		
		
	}
	
	private void insertCustomerInfoIntoDatabase() 
	{
		System.out.println("Attemping to add the customer information to the database.");
		try
		{
			this.statement = this.connection.createStatement(); //create an instance
			this.query ="insert into customer(salutation,first_name,last_name,credit_card_num,"
					+ "credit_card_expire, street, city, state, phone, email)"
					+ "values('" + this.salutation + "','" + this.firstName + "','" + this.lastName + 
					"','" + this.creditCardNumber + 
					"',to_date('" +this.creditCardExpirationDay+ "-" + this.creditCardExpirationMonth + 
					"-" + this.creditCardExpirationYear + "', 'DD-MON-YYYY'),'" + this.streetAddress + 
					"','" + this.city + "','" + this.state + "','" + this.phoneNumber + "','" + this.email + "')";
			statement.executeUpdate(query);
			connection.commit();
			
			System.out.println();
			connection.commit();
			
			int customerCID = queryDatabaseForCID(this.firstName, this.lastName);
			System.out.printf("Successfully added %s %s to the database.\n",this.firstName, this.lastName);
			System.out.printf("Your PittRewards number is %d\n", customerCID);
		}
		catch(Exception Ex) 
		{
			System.out.println("There was trouble in insertCustomerInfoIntoDatabase()" +
					   Ex.toString());
		}
		
	}

	private int queryDatabaseForCID(String queryFirstName, String queryLastName) 
	{
		System.out.println("Attemping to add the customer information to the database.");
		try
		{
			this.statement = this.connection.createStatement(); //create an instance
			this.query = "select cid from customer where first_name='" + queryFirstName +
					"' and last_name='" + queryLastName+ "'";
			this.resultSet = statement.executeQuery(query);
			
			int customerID = -1;
			
			while(resultSet.next())
			{
				//System.out.printf("\n%s : %s : %s", resultSet.getString(1), resultSet.getString(2), resultSet.getString(3));
				customerID = Integer.parseInt(resultSet.getString(1));	
			}
			
			return customerID;
			
		}
		catch(Exception e) 
		{
			System.out.println("There was trouble in insertCustomerInfoIntoDatabase()" +
					   e.toString());
			e.printStackTrace();
			return -1;
		}
		
	}

	private boolean customerNameIsInDatabaseAlready()
	{
		
		try
		{
			this.statement = this.connection.createStatement(); //create an instance
			System.out.println("Is the customer in the database yet?");
			this.query = "select cid, first_name, last_name from customer";
			//statement.executeUpdate(query);
			this.resultSet = statement.executeQuery(query);
			
			String queryFirstName = "";
			String queryLastName = "";
			
			while(resultSet.next())
			{
				System.out.printf("\n%s : %s : %s", resultSet.getString(1), resultSet.getString(2), resultSet.getString(3));
				queryFirstName = resultSet.getString(2);
				queryLastName = resultSet.getString(3);
				if(queryFirstName.equals(this.firstName) && queryLastName.equals(this.lastName))					
				{
					System.out.println("It appears your first- and last-name combination already exists.");
					return true;
				}
				else
				{
					continue;
				}
			}
			System.out.println();
			//connection.commit();
			
		}
		catch(Exception Ex) 
		{
			System.out.println("Error running the sample queries.  Machine Error: " +
					   Ex.toString());
		}
		System.out.println("No, the current user has a unique first- and last-name combination.");
		return false;
	}
	
	private void askCustomerForCreditCardExpirationDates()
	{
		try
		{
			BufferedReader br = new BufferedReader(new InputStreamReader(System.in));
			String msg = "\nEnter the day of the month your credit card expires (1-31): ";
			while(true)
			{
				System.out.print(msg);
				try
				{
					this.creditCardExpirationDay = Integer.parseInt(br.readLine());
					if(creditCardExpirationDay > 0 && creditCardExpirationDay < 32)
					{
						break;
					}
					else
					{
						continue;
					}
				}
				catch(Exception e)
				{
					e.printStackTrace();
					continue;
				}				
			}
			
			msg = "\nEnter the month of the year your credit card expires (1-12): ";
			while(true)
			{
				System.out.print(msg);
				try
				{
					int monthOfYear = -1;
					monthOfYear = Integer.parseInt(br.readLine());
					if(monthOfYear > 0 && monthOfYear < 13)
					{
						this.creditCardExpirationMonth = setMonthOfYearToOracleString(monthOfYear);
						break;
					}
					else
					{
						continue;
					}
				}
				catch(Exception e)
				{
					e.printStackTrace();
					continue;
				}				
			}
			msg = "\nEnter the year your credit card expires ('1991', '2013', etc): ";
			while(true)
			{
				System.out.print(msg);
				
				this.creditCardExpirationYear = br.readLine();
				if(creditCardExpirationYear.length() == 4)
				{
					break;
				}
				else
				{
					continue;
				}
			}
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
	}
	
	private String setMonthOfYearToOracleString(int monthOfYear) 
	{
		switch(monthOfYear)
		{
			case 1:
				return "Jan";
				
			case 2:
				return "Feb";
				
			case 3:
				return "Mar";
				
			case 4:
				return "Apr";
				
			case 5:
				return "May";
				
			case 6:
				return "Jun";
				
			case 7:
				return "Jul";
				
			case 8:
				return "Aug";
				
			case 9:
				return "Sep";
				
			case 10:
				return "Oct";
				
			case 11:
				return "Nov";
				
			case 12:
				return "Dec";
				
			default:
				System.out.println(monthOfYear + " is not a month of the year. It should be 1-12.");
				return "";
		}
	}

	private void askCustomerForCreditCardNumber()
	{
		try
		{
			BufferedReader br = new BufferedReader(new InputStreamReader(System.in));
			String msg = "\nEnter your 16-digit credit number with or without hypens ('-'): ";
			while(true)
			{
				System.out.print(msg);
				this.creditCardNumber = br.readLine();
				if(creditCardNumber.length() == 16)
				{
					break;
				}
				else if(creditCardNumber.length() == 19)
				{
					creditCardNumber = creditCardNumber.replace("-", "");
					break;
				}
				else
				{
					continue;
				}
				
			}
			
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
	}
	
	private void askCustomerForEmail()
	{
		try
		{
			BufferedReader br = new BufferedReader(new InputStreamReader(System.in));
			String msg = "\nEnter your email address: ";
			while(true)
			{
				System.out.print(msg);
				this.email = br.readLine();
				if(email.length() < 30)
				{
					break;
				}
				else
				{
					continue;
				}
				
			}
			
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
	}
	
	private void askCustomerForPhoneNumber()
	{
		try
		{
			BufferedReader br = new BufferedReader(new InputStreamReader(System.in));
			String enterPhoneNumber = "\nEnter your phone number (412-123-4567 or 4121234567): ";
			while(true)
			{
				System.out.print(enterPhoneNumber);
				this.phoneNumber = br.readLine();
				phoneNumber.replace("-", "");
				if(phoneNumber.length() == 10)
				{
					break;
				}
				else
				{
					continue;
				}
				
			}
			
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
	}
	
	private void askCustomerForName()
	{
		try
		{
			BufferedReader br = new BufferedReader(new InputStreamReader(System.in));
						
			String enterSalutation = "\nsalutation (Mr/Mrs/Ms): ";
			while(true)
			{
				System.out.print(enterSalutation);
				this.salutation = br.readLine();
				if(salutation.equals("Mr") || salutation.equals("Mrs") || salutation.equals("Ms"))
				{
					break;
				}
				else
				{
					continue;
				}
			}
		
			String enterFirstName = "\nEnter your first name: ";
			
			while(true)
			{
				System.out.print(enterFirstName);
				this.firstName = br.readLine();
				if(firstName.length() > 30)
				{		
					System.out.print("\nYour name is too long. Keep it to 30 characters.");
					continue;
				}
				else
				{
					if(Character.isUpperCase((firstName.charAt(0))))
					{
						this.firstName = firstName.charAt(0) + firstName.substring(1).toLowerCase();
						break;
					}
					else
					{
						System.out.println("The first letter of your name should have a capital letter.");
						continue;
					}
				}
			}
			String enterLastName = "\nEnter your last name: ";
			
			while(true)
			{
				System.out.print(enterLastName);
				this.lastName = br.readLine();
				if(lastName.length() > 30)
				{		
					System.out.print("\nYour name is too long. Keep it to 30 characters.");
					continue;
				}
				else
				{
					if(Character.isUpperCase((lastName.charAt(0))))
					{
						this.firstName = firstName.charAt(0) + firstName.substring(1).toLowerCase();
						break;
					}
					else
					{
						System.out.println("The first letter of your name should have a capital letter.");
						continue;
					}
				}
			}				
			if(customerNameIsInDatabaseAlready())
			{
				askCustomerForName();
			}
			else
			{
				// the first and last name combination was unique
			}
		}
		catch(Exception e)
		{
			System.out.println("Problem reading in data from the user. Quitting Task 1.");
			return;
		}
		System.out.println();

	}
	
	private void askCustomerForAddress()
	{
		try
		{
			BufferedReader br = new BufferedReader(new InputStreamReader(System.in));			
			
			String enterStreetAddress = "\nEnter your street address: ";
			
			while(true)
			{
				System.out.print(enterStreetAddress);
				this.streetAddress = br.readLine();
				if(streetAddress.length() > 30)
				{		
					System.out.print("\nYour address is too long. Keep it to 30 characters.");
					continue;
				}
				else
				{
					break;
				}
			}
			
			String enterCity = "\nEnter your city: ";

			while(true)
			{
				System.out.print(enterCity);
				this.city = br.readLine();
				if(city.length() > 30)
				{		
					System.out.print("\nYour city is too long. Keep it to 30 characters.");
					continue;
				}
				else
				{
					break;
				}
			}
			String enterState = "\nEnter your state (postal abbreviation): ";
			
			while(true)
			{
				System.out.print(enterState);
				this.state = br.readLine();
				if(state.length() != 2)
				{		
					System.out.print("\nEnter your state abbreviation: this is two characters.");
					continue;
				}
				else
				{
					break;
				}
			}
		}
		catch(Exception e)
		{
			System.out.println("Problem reading in data from the user. Quitting Task 1.");
			return;
		}
		System.out.println();
	}

}

